import os
import django
from datetime import datetime, time

# 设置Django环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'basal_adjust.settings')
django.setup()

from django.db import connection

def test_query():
    """测试基本SQL查询"""
    print("测试SQL查询兼容性")
    
    # 测试1: 简单查询
    with connection.cursor() as cursor:
        try:
            query = "SELECT 1"
            cursor.execute(query)
            result = cursor.fetchone()
            print(f"测试1 成功: {result}")
        except Exception as e:
            print(f"测试1 失败: {e}")
    
    # 测试2: qmark风格占位符查询
    with connection.cursor() as cursor:
        try:
            query = "SELECT ? as test_param"  # 问号样式
            param = ("test_value",)  # 单一参数要用逗号创建元组
            print(f"执行: {query} 参数: {param}")
            cursor.execute(query, param)
            result = cursor.fetchone()
            print(f"测试2 成功: {result}")
        except Exception as e:
            print(f"测试2 失败: {e}")
    
    # 测试2-1: 尝试named风格占位符
    with connection.cursor() as cursor:
        try:
            query = "SELECT :value as test_param"  # 命名样式
            param = {"value": "test_value"}
            print(f"执行: {query} 参数: {param}")
            cursor.execute(query, param)
            result = cursor.fetchone()
            print(f"测试2-1 成功: {result}")
        except Exception as e:
            print(f"测试2-1 失败: {e}")
    
    # 测试3: 检查patient_info表
    with connection.cursor() as cursor:
        try:
            query = "SELECT COUNT(*) FROM patient_info"
            cursor.execute(query)
            result = cursor.fetchone()[0]
            print(f"测试3 成功: patient_info表中有 {result} 条记录")
        except Exception as e:
            print(f"测试3 失败: {e}")
    
    # 测试4: 检查test001患者 - qmark风格
    with connection.cursor() as cursor:
        try:
            patient_number = "test001"
            query = "SELECT COUNT(*) FROM patient_info WHERE patient_number = ?"
            params = (patient_number,)
            print(f"执行: {query} 参数: {params}")
            cursor.execute(query, params)
            result = cursor.fetchone()[0]
            print(f"测试4 成功: 患者 {patient_number} 存在记录数: {result}")
        except Exception as e:
            print(f"测试4 失败: {e}")
    
    # 测试5: 检查实际使用的查询
    with connection.cursor() as cursor:
        try:
            patient_number = "test001"
            query = f"SELECT 1 FROM cgm_{patient_number} LIMIT 1"
            cursor.execute(query)
            result = cursor.fetchone()
            print(f"测试5 成功: cgm_{patient_number}表存在")
        except Exception as e:
            print(f"测试5 失败: cgm_{patient_number}表不存在 - {e}")
            
    # 测试6: 检查特定日期的数据 - 测试多参数
    with connection.cursor() as cursor:
        try:
            patient_number = "test001"
            today = datetime.now().date()
            start_date = datetime.combine(today, time(0, 0))
            end_date = datetime.combine(today, time(23, 59, 59))
            
            table_name = f"cgm_{patient_number}"
            query = f"""
                SELECT COUNT(*) FROM {table_name}
                WHERE sg_date BETWEEN ? AND ?
            """
            params = (start_date, end_date)
            print(f"执行: {query} 参数: {params}")
            cursor.execute(query, params)
            result = cursor.fetchone()[0]
            print(f"测试6 成功: {table_name}表在日期范围内有 {result} 条记录")
        except Exception as e:
            print(f"测试6 失败: {e}")
    
if __name__ == "__main__":
    test_query() 